package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.*;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.EmployeeDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.EmployeeDTO;
import cam.School.DataTransfertObject.StudentDTO;

public class EmployeeDAOImpl implements EmployeeDAO {

	// Constants
	// ----------------------------------------------------------------------------------

	private static final String QUERY_PARAMS = "employee.id, firstname, matriculationNumber,"
			+ " password, userPrivileg, createdby, firstLogin, lastLogin,"
			+ " lastname, street, city, zipCode, country, email, cellPhone, workPhone, profession,"
			+ " personToCallByEmergencyCase, numberToCallByEmergencyCase, birthday, placeOfBirth,"
			+ " employee.firstRegistration, employee.lastUpdate, isPrincipalTeacherOfClassRoom,"
			+ " firstLogin, lastLogin, createdby, userPrivileg, iban, bic, gender, maritalStatus";
	
	private static final String BY_ClASSNAME_QUERY_PARAMS = "subject.id as subjectId,"  
			+"subject.className, subjectprofessor.id as subjectprofessorId, "
			+"subjectprofessor.professorId as professorId, "
			+"subjectprofessor.subjectId, ";
	
	
	
	private static final String BY_ClASSGROUP_QUERY_PARAMS = "classroom.firstRegistration as classroomFirstRegistration,"  
			+"subject.id as subjectId , infrastructurcategory.id as infrastructurcategoryId, "
			+"classroom.id as classroomId, "
			+"subjectprofessor.id as subjectprofessorId, ";
		    
	private static final String INSERT_PARAMS = "(firstname, lastname, street, city,"
			+ " zipCode, country, birthday, email, cellPhone, workPhone, isPrincipalTeacherOfClassRoom,"
			+ " personToCallByEmergencyCase, numberToCallByEmergencyCase, isDeleted,"
			+ " firstRegistration, lastUpdate, matriculationNumber, profession, placeOfBirth,"
			+ " firstLogin, lastLogin, createdby, isOnline, password, userPrivileg, iban, bic, gender, maritalStatus)";


	private static final String UPDATE_QUERY_PARAMS = "firstname = ?, matriculationNumber = ?,"
			+ " password = ?, userPrivileg = ?, lastLogin = ?,"
			+ " lastname = ?, street = ?, city = ?, zipCode = ?, country = ?,"
			+ " email = ?, cellPhone = ?, workPhone = ?, profession = ?,"
			+ " personToCallByEmergencyCase = ?, numberToCallByEmergencyCase = ?,"
			+ " birthday = ?, placeOfBirth = ?,"
			+ " lastUpdate = ?, isPrincipalTeacherOfClassRoom = ?,"
			+ " iban = ?, bic = ?, gender = ?, maritalStatus = ?";

	private static final String SQL_FIND_ALL = "SELECT "
			+ QUERY_PARAMS
			+ " FROM employee WHERE isDeleted = 0";
	
	private static final String SQL_FIND_BY_CLASSNAME = "SELECT "
			+  BY_ClASSNAME_QUERY_PARAMS + QUERY_PARAMS
			+" FROM subject"
			+" inner join subjectprofessor"
			+" on subject.id = subjectprofessor.subjectId"
			+" inner join employee"
			+" on employee.id = subjectprofessor.professorId"
			+" where className = ? AND isDeleted = 0";
	
	private static final String SQL_FIND_BY_CLASSGROUP = "SELECT "
			+  BY_ClASSGROUP_QUERY_PARAMS + QUERY_PARAMS
			+" FROM infrastructurcategory " 
			+" inner join classroom on infrastructurcategory.id = classroom.infrastructureCategoryId "
			+" inner join subject on subject.className = classroom.name "
			+" inner join subjectprofessor on subject.id = subjectprofessor.subjectId " 
			+" inner join employee on employee.id = subjectprofessor.professorId " 
			+" where infrastructurcategory.name = ? AND employee.isDeleted = 0 ";
	
	private static final String SQL_FIND_IMAGE_BY_USER_ID = "SELECT location " 
			+ " FROM Image WHERE  objectId = ?";
	
	private static final String SQL_FIND_BY_ID = "SELECT "
			+ QUERY_PARAMS
			+ " FROM employee WHERE  id = ? AND isDeleted = 0";
	
	
	private static final String SQL_FIND_BY_USERNAME_PASSWORD = "SELECT "
			+ QUERY_PARAMS
			+ " FROM employee WHERE lastname = ? AND password = MD5(?) AND isDeleted = 0  and isOnline = 0";
	
	private static final String SQL_FIND_BY_FIRSTNAME_AND_LASTNAME = "SELECT "
			+ QUERY_PARAMS
			+ " FROM employee WHERE firstname = ? AND lastname = ? AND isDeleted = 0";

	private static final String SQL_FIND_BY_FIRSTNAME_AND_BIRTHDAY = "SELECT "
			+ QUERY_PARAMS
			+ " FROM employee WHERE AND birthday = ? AND lastname = ? AND isDeleted = 0";

	private static final String SQL_UPDATE = "UPDATE employee SET "
			+ UPDATE_QUERY_PARAMS
			+ " FROM employee WHERE id = ? AND isDeleted = 0";
	
	private static final String SQL_FIND_BY_IMMATRICULATION_NUMBER =
            "SELECT "+ QUERY_PARAMS +" FROM employee WHERE  matriculationNumber = ? AND isDeleted = 0";
    
	
	private static final String SQL_LOG_IN_OR_OUT = "UPDATE employee SET isOnline = ?"
			+ " WHERE username = ? AND password = MD5(?) AND isDeleted = 0";

	private static final String SQL_INSERT = "INSERT INTO employee " +INSERT_PARAMS+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private static final String SQL_INSERT_IMAGE = "INSERT INTO image(objectId, location) VALUES (?, ?)";

	
	private static final String SQL_DELETE_BY_ID = " UPDATE employee SET isDeleted = 1 WHERE id = ?";

	private static final String SQL_DELETE_SUBJECT_PROFESSOR_BY_ID = "DELETE FROM subjectprofessor WHERE professorId = ?";

	private static final String SQL_DELETE_PICTURE_PROFESSOR_BY_ID = "DELETE FROM image WHERE objectId = ?";
	// Vars
	// ---------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors
	// -------------------------------------------------------------------------------

	/**
	 * Construct an employee DAO for the given DAOFactory. Package private so
	 * that it can be constructed inside the DAO package only.
	 * 
	 * @param daoFactory
	 *            The DAOFactory to construct this employee DAO for.
	 */
	public EmployeeDAOImpl(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}
	
	private EmployeeDTO map(ResultSet resultSet)
			throws SQLException {
		EmployeeDTO employee = new EmployeeDTO();
		employee.setId(resultSet.getLong("id"));
		employee.setFirstname(resultSet.getString("firstname"));
		employee.setLastname(resultSet.getString("lastname"));
		employee.setStreet(resultSet.getString("street"));
		employee.setCity(resultSet.getString("city"));
		employee.setZipCode(resultSet.getString("zipCode"));
		employee.setCountry(resultSet.getString("country"));
		employee.setEmail(resultSet.getString("email"));
		employee.setMatriculationNumber(resultSet.getString("matriculationNumber"));
		employee.setBirthday(resultSet.getDate("birthday"));
		employee.setIsPrincipalTeacherOfClassRoom(resultSet.getString("isPrincipalTeacherOfClassRoom"));
		employee.setProfession(resultSet.getString("profession"));
		employee.setPlaceOfBirth(resultSet.getString("placeOfBirth"));
		employee.setCellPhone(resultSet.getString("cellPhone"));
		employee.setWorkPhone(resultSet.getString("workPhone"));
		employee.setNumberToCallByEmergencyCase(resultSet
				.getString("numberToCallByEmergencyCase"));
		employee.setPersonToCallByEmergencyCase(resultSet
				.getString("personToCallByEmergencyCase"));
		employee.setFirstRegistration(resultSet.getDate("firstRegistration"));
		employee.setLastUpdate(resultSet.getDate("lastUpdate"));
		employee.setFirstLogin(resultSet.getDate("firstLogin"));
		employee.setLastLogin(resultSet.getDate("lastLogin"));
		employee.setCreatedBy(resultSet.getString("createdby"));
		employee.setPassword(resultSet.getString("password"));
		employee.setUserPrivileg(resultSet.getString("userPrivileg"));
		employee.setGender(resultSet.getString("gender"));
		employee.setMaritalStatus(resultSet.getString("maritalStatus"));
		employee.setIban(resultSet.getString("iban"));
		employee.setBic(resultSet.getString("bic"));
		employee.setPictureLocationPath(getUserImagePathLocation(employee.getId()));	
		return employee;
	}
	
	private String getUserImagePathLocation(long userId){
		return getUserImagePathLocation(SQL_FIND_IMAGE_BY_USER_ID, userId);		
	}

	private String getUserImagePathLocation(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getString("location");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return "default Location"; //TO DO 
	}

	@Override
	public List<EmployeeDTO> getAll() throws DAOException {
		Object[] values = {};
		return getAll(SQL_FIND_ALL, values);
	}
	
	
	
	public List<EmployeeDTO> getAll(String sqlQuery, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<EmployeeDTO> employees = new ArrayList<EmployeeDTO>();

		try {
			connection = daoFactory.getConnection();
			if(values.length == 0 ){
				preparedStatement = connection.prepareStatement(sqlQuery);
			}else{
				preparedStatement = prepareStatement(connection, sqlQuery, false, values);
			}
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				employees.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return employees;
	}
	
	public List<EmployeeDTO> getByRange(int startIndex, int count) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<EmployeeDTO> employees = new ArrayList<EmployeeDTO>();

		try {
			connection = daoFactory.getConnection();
			String SQL_FIND_BY_RANGE = "SELECT "
					+ QUERY_PARAMS + " FROM employee"
					+ " WHERE isDeleted = 0 LIMIT "+ Integer.toString(startIndex) +" , "+ Integer.toString(count);
			preparedStatement = connection.prepareStatement(SQL_FIND_BY_RANGE);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				employees.add(map(resultSet));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return employees;
	}

	private EmployeeDTO find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		EmployeeDTO employee = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				employee = map(resultSet);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return employee;
	}

	@Override
	public EmployeeDTO getById(Long id) throws DAOException {
		return find(SQL_FIND_BY_ID, id);
	}

	@Override
	public CallResult create(EmployeeDTO employee) throws IllegalArgumentException,
			DAOException {

		Connection connection = null;
		CallableStatement callableStatement = null;
		long result;
		try {
			connection = daoFactory.getConnection();
			
			callableStatement = connection.prepareCall("{call createEmployeeProcedure(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");			
			
			callableStatement.registerOutParameter(1, java.sql.Types.BIGINT);
			callableStatement.setString(2, employee.getFirstname());
			callableStatement.setString(3, employee.getLastname());
			callableStatement.setString(4, employee.getStreet());
			callableStatement.setString(5, employee.getCity());
			callableStatement.setString(6, employee.getZipCode());
			callableStatement.setString(7, employee.getCountry());
			callableStatement.setDate(8, toSqlDate(employee.getBirthday()));						
			callableStatement.setString(9, employee.getEmail());
			callableStatement.setString(10, employee.getCellPhone());
			callableStatement.setString(11, employee.getWorkPhone());
			callableStatement.setString(12, employee.getIsPrincipalTeacherOfClassRoom());
			callableStatement.setString(13, employee.getPersonToCallByEmergencyCase());
			callableStatement.setString(14, employee.getNumberToCallByEmergencyCase());
			callableStatement.setBoolean(15, false);
			callableStatement.setDate(16, toSqlDate(new Date()));
			callableStatement.setDate(17, toSqlDate(new Date()));
			callableStatement.setString(18, employee.getMatriculationNumber());
			callableStatement.setString(19, employee.getProfession());
			callableStatement.setString(20, employee.getPlaceOfBirth());
			callableStatement.setDate(21, toSqlDate(new Date()));
			callableStatement.setDate(22, toSqlDate(new Date()));
			callableStatement.setString(23, employee.getCreatedBy());
			callableStatement.setBoolean(24, false);
			callableStatement.setString(25, employee.getPassword());
			callableStatement.setString(26, employee.getUserPrivileg());
			callableStatement.setString(27, employee.getIban());
			callableStatement.setString(28, employee.getBic());
			callableStatement.setString(29, employee.getGender());
			callableStatement.setString(30, employee.getMaritalStatus());		
			
			callableStatement.executeUpdate();			
			result = callableStatement.getLong(1);
			
			if(new Integer((int)result).equals(new Integer(0))){
				return new CallResult(406,"not duplicated allowed");
			}
			
		} catch (SQLException e) {
			return new CallResult(406,e.toString());
		} finally {
			close(connection, callableStatement);
		}
		return new CallResult(result,"success") ;
	}
	
	public Object [] getValue(EmployeeDTO employee){
		Object[] values = { 
				employee.getFirstname(),
				employee.getLastname(), 
				employee.getStreet(),
				employee.getCity(), 
				employee.getZipCode(),
				employee.getCountry(),
				employee.getBirthday(),
				employee.getEmail(),
				employee.getCellPhone(), 
				employee.getWorkPhone(),
				employee.getIsPrincipalTeacherOfClassRoom(),
				employee.getPersonToCallByEmergencyCase(),
				employee.getNumberToCallByEmergencyCase(),
				false,
				toSqlDate(employee.getFirstRegistration()),
				toSqlDate(employee.getLastUpdate()),
				employee.getMatriculationNumber(),
				//employee.getPictureLocationPath(), 
				employee.getProfession(),
				employee.getPlaceOfBirth(),
				toSqlDate(employee.getFirstLogin()),
				toSqlDate(employee.getLastLogin()),
				employee.getCreatedBy(),
				false,
				employee.getPassword(),
				employee.getUserPrivileg(),
				employee.getIban(),
				employee.getBic(),
				employee.getGender(),
				employee.getMaritalStatus()
		};
		return values;
		
	}

	public int executeUpdateStatement(String sql, Object[] values) throws DAOException {

		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection,
					sql, false, values);
			int affectedRowCount = preparedStatement.executeUpdate();

			if(affectedRowCount  == 1){
				return 200;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement);
		}
		return 304;
	}
	
	@Override
	public int update(String username, long id) throws DAOException {
		Object []params = {0, username, id};
		return executeUpdateStatement(SQL_LOG_IN_OR_OUT, params);
	}

	@Override
	public CallResult delete(long employeeId) throws Exception {
		Connection connection = null;
		PreparedStatement deleteByIdPreparedStatement = null;
		PreparedStatement deleteSubjectsPreparedStatement = null;
		PreparedStatement deletePicturePreparedStatement = null;
				
		try {
			connection = daoFactory.getConnection();			
			connection.setAutoCommit(false);			
			deleteByIdPreparedStatement = connection.prepareStatement(SQL_DELETE_BY_ID);
			deleteByIdPreparedStatement.setLong(1, employeeId);
			deleteByIdPreparedStatement.executeUpdate(); 
			 
			deleteSubjectsPreparedStatement = connection.prepareStatement(SQL_DELETE_SUBJECT_PROFESSOR_BY_ID);
			deleteSubjectsPreparedStatement.setLong(1, employeeId);
			deleteSubjectsPreparedStatement.executeUpdate(); 
			
			deletePicturePreparedStatement = connection.prepareStatement(SQL_DELETE_PICTURE_PROFESSOR_BY_ID);
			deletePicturePreparedStatement.setLong(1, employeeId);
			deletePicturePreparedStatement.executeUpdate();
			
			connection.commit();
			
		} catch (SQLException e) {
			connection.rollback();
			new CallResult(405, e.toString());
		} finally {
			close(connection, deleteByIdPreparedStatement);
			close(connection, deleteSubjectsPreparedStatement);
			close(connection, deletePicturePreparedStatement);
		}
		return new CallResult(200,"sucess");
	}

	@Override
	public EmployeeDTO getByFirstName(String firstName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public EmployeeDTO getByFirstAndLastName(String firstName, String lastName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public EmployeeDTO getByLastNameAndBirthday(Date birthday, String lastName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public EmployeeDTO getByProfession(String profession) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public EmployeeDTO getByImmatricutionNumber(String immatriculationNumber) {
		Object[] values = {immatriculationNumber};
		List<EmployeeDTO> employees = getAll(SQL_FIND_BY_IMMATRICULATION_NUMBER, values);
		
		if(employees.size() > 0){
			return employees.get(0);
		}
		return null;		
	}

	@Override
	public EmployeeDTO find(String username, String password) {
		return find(SQL_FIND_BY_USERNAME_PASSWORD, username, password);
	}

	@Override
	public CallResult update(EmployeeDTO employee) throws IllegalArgumentException,
			DAOException {
		
        Object[] values = {
        		employee.getFirstname(),
        		employee.getMatriculationNumber(),
        		employee.getPassword(),
        		employee.getUserPrivileg(),
        		employee.getLastLogin(),
        		employee.getLastname(),
        		employee.getStreet(),
        		employee.getCity(),
        		employee.getZipCode(),
        		employee.getCountry(),
        		employee.getEmail(),
        		employee.getCellPhone(),
        		employee.getWorkPhone(),
        		employee.getProfession(),
        		employee.getPersonToCallByEmergencyCase(),
        		employee.getNumberToCallByEmergencyCase(),  
        		toSqlDate(employee.getBirthday()),
        		employee.getPlaceOfBirth(),
        		employee.getLastUpdate(),
        		employee.getIsPrincipalTeacherOfClassRoom(),
        		employee.getIban(),
        		employee.getBic(),
        		employee.getGender(),
        		employee.getMaritalStatus(),          
        		employee.getId()
        };
                
        
        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_UPDATE, false, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
            	return new CallResult(406, "Updating employee failed, no rows affected.");
            }
        } catch (SQLException e) {
        	return new CallResult(406, e.toString());
        }
        return new CallResult(200, "success");		
	}

	@Override
	public List<EmployeeDTO> getProfessorBySubjectName(String className) {
		Object[] values = {className};
		return getAll(SQL_FIND_BY_CLASSNAME, values);		
	}
	
	@Override
	public List<EmployeeDTO> getProfessorByClassGroup(String classGroup) {
		Object[] values = {classGroup};
		return getAll(SQL_FIND_BY_CLASSGROUP, values);		
	}
}
